package org.zjvis.datascience.service.gis;

import static org.zjvis.datascience.common.constant.DatabaseConstant.DEFAULT_DATASET_ID;
import static org.zjvis.datascience.common.gis.GisUtils.GisConstant.GEO_JSON_UNUSED_COLUMNS;
import static org.zjvis.datascience.common.gis.GisUtils.GisConstant.PRE_LOAD_PARTITION_NUMBERS;
import static org.zjvis.datascience.common.gis.GisUtils.GisConstant.SLICE_GEOJSON_EXPIRE_TIME;
import static org.zjvis.datascience.common.gis.GisUtils.GisConstant.SLICE_META_DATA_EXPIRE_TIME;
import static org.zjvis.datascience.common.gis.GisUtils.GisUtil.GIS_TYPE_DIC;
import static org.zjvis.datascience.common.gis.GisUtils.GisUtil.addAllSql;
import static org.zjvis.datascience.common.gis.GisUtils.GisUtil.addGeom4326ColumnSql;
import static org.zjvis.datascience.common.gis.GisUtils.GisUtil.bufferUnitConverter;
import static org.zjvis.datascience.common.gis.GisUtils.GisUtil.calAreaSql;
import static org.zjvis.datascience.common.gis.GisUtils.GisUtil.calBufferSql;
import static org.zjvis.datascience.common.gis.GisUtils.GisUtil.calCentroidSql;
import static org.zjvis.datascience.common.gis.GisUtils.GisUtil.calLengthSql;
import static org.zjvis.datascience.common.gis.GisUtils.GisUtil.calPerimeterSql;
import static org.zjvis.datascience.common.gis.GisUtils.GisUtil.constructGeoJsonCacheKey;
import static org.zjvis.datascience.common.gis.GisUtils.GisUtil.constructGisSliceCacheKey;
import static org.zjvis.datascience.common.gis.GisUtils.GisUtil.countElements;
import static org.zjvis.datascience.common.gis.GisUtils.GisUtil.createLayerByNearIds;
import static org.zjvis.datascience.common.gis.GisUtils.GisUtil.createViewSql;
import static org.zjvis.datascience.common.gis.GisUtils.GisUtil.createViewSql4326;
import static org.zjvis.datascience.common.gis.GisUtils.GisUtil.geomToGeoJsonSql;
import static org.zjvis.datascience.common.gis.GisUtils.GisUtil.geomToGeojsonSql;
import static org.zjvis.datascience.common.gis.GisUtils.GisUtil.geomTypeSql;
import static org.zjvis.datascience.common.gis.GisUtils.GisUtil.getDateStat;
import static org.zjvis.datascience.common.gis.GisUtils.GisUtil.getDistinctCount;
import static org.zjvis.datascience.common.gis.GisUtils.GisUtil.getNearElementsId;
import static org.zjvis.datascience.common.gis.GisUtils.GisUtil.getNumericalStat;
import static org.zjvis.datascience.common.gis.GisUtils.GisUtil.getTbColumnName;
import static org.zjvis.datascience.common.gis.GisUtils.GisUtil.newGeomTypeSql;
import static org.zjvis.datascience.common.gis.GisUtils.GisUtil.queryViewCol;
import static org.zjvis.datascience.common.gis.GisUtils.GisUtil.updateGeom4326ColumnSql;

import cn.hutool.core.util.ObjectUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Objects;
import java.util.Set;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

import com.google.common.collect.Lists;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.zjvis.datascience.common.dto.DashboardDTO;
import org.zjvis.datascience.common.dto.gis.GisLayersDTO;
import org.zjvis.datascience.common.dto.gis.SliceDTO;
import org.zjvis.datascience.common.enums.PythonDateTypeFormatEnum;
import org.zjvis.datascience.common.gis.GisUtils.GisUtil;
import org.zjvis.datascience.common.pool.BasePool;
import org.zjvis.datascience.common.sql.SqlHelper;
import org.zjvis.datascience.common.util.DatasetUtil;
import org.zjvis.datascience.common.util.JwtUtil;
import org.zjvis.datascience.common.util.RedisUtil;
import org.zjvis.datascience.common.util.SqlUtil;
import org.zjvis.datascience.common.util.db.JDBCUtil;
import org.zjvis.datascience.common.vo.gis.GisLayersVO;
import org.zjvis.datascience.common.vo.gis.GisNumericalResVO;
import org.zjvis.datascience.service.dataprovider.GPDataProvider;
import org.zjvis.datascience.service.dataset.DatasetService;
import org.zjvis.datascience.service.mapper.DashboardMapper;

@Service
public class GisAnalysisService {

    protected final static Logger logger = LoggerFactory.getLogger(GisAnalysisService.class);

    @Autowired
    private GPDataProvider gpDataProvider;

    @Autowired
    private RedisUtil redisUtil;

    @Autowired
    private DashboardMapper dashboardMapper;

    @Autowired
    private GisLayersService gisLayersService;

    @Autowired
    private DatasetService datasetService;

    @Autowired
    private BasePool basePool;


    /**
     * 分页操作, 每1MB, 操作一次数据库
     */
    public static long OPERATE_SLICE_BYTES = 1024 * 1024L;
    public static long OPERATE_MAX_SLICE_NUMBERS = 100L;


    /**
     * 分页查询, 每5MB, 查询一次数据库
     */
    public static long DEFAULT_QUERY_SLICE_BYTES = 5 * 1024 * 1024L;
    public static long DEFAULT_QUERY_MAX_SLICE_NUMBERS = 100L;

//    /**
//     * 创建新view
//     *
//     * @param tbName
//     * @param projectId
//     * @param tabId
//     * @param layerId
//     * @return view名
//     */
//    public String createView(String tbName, Long projectId, Long tabId, Long layerId) {
//        List<String> viewData = createViewSql(tbName, projectId, tabId, layerId);
//        updateDB(viewData.get(1));
//        return viewData.get(0);
//    }

    public void addAndUpdateGeom4326Column(String tbName, String geomType, String srid, String proj4Text) {
        String addGeom4326ColumnSql = addGeom4326ColumnSql(tbName, geomType);
        updateDB(addGeom4326ColumnSql);
        updateGeom4326Column(tbName, srid, proj4Text);
    }

    private void updateGeom4326Column(String tbName, String srid, String proj4Text) {
        List<SliceDTO> sliceDTOList = getSliceDTO(tbName, OPERATE_SLICE_BYTES,
                OPERATE_MAX_SLICE_NUMBERS);
        List<CompletableFuture<Void>> completableFutureList = sliceDTOList.stream().map(
                sliceDTO -> {
                    String updateSql = updateGeom4326ColumnSql(tbName, srid, proj4Text, sliceDTO.getOffset(),
                            sliceDTO.getPageSize());
                    return CompletableFuture.runAsync(() -> updateDB(updateSql), basePool.getExecutor());
                }).collect(Collectors.toList());
        try {
            CompletableFuture.allOf(completableFutureList.toArray(new CompletableFuture[completableFutureList.size()]))
                    .get();
        } catch (ExecutionException | InterruptedException e) {
            logger.info("execute updateGeom4326Column error", e);
        }
    }

    /**
     * 创建转换经纬度的新view
     *
     * @param tbName
     * @param projectId
     * @param tabId
     * @param layerId
     * @return view名
     */
    public String createView(String tbName, Long projectId, Long tabId, Long layerId) {
//        String getTbColWithoutGeomSql = getTbColWithoutGeom(tbName);
//        List<String> tbCols = queryFromDB(getTbColWithoutGeomSql);
        List<String> viewData = createViewSql(tbName, projectId, tabId, layerId);
        updateDB(viewData.get(1));
        return viewData.get(0);
    }

    public String createView4326(String tbName, Long projectId, Long tabId, Long layerId, String srid) {
//        String getTbColWithoutGeomSql = getTbColWithoutGeom(tbName);
//        List<String> tbCols = queryFromDB(getTbColWithoutGeomSql);
        List<String> viewData = createViewSql4326(srid, tbName, projectId, tabId, layerId);
        updateDB(viewData.get(1));
        return viewData.get(0);
    }


//    public String getSRID(String sourceTbName) {
//        String getSRIDSql = getSRIDSql(sourceTbName);
//        List<String> res = queryFromDB(getSRIDSql);
//        String srid = res.get(0);
//        return srid;
//    }

    /**
     * 返回view表中数值类型的字段名并去除record_id
     *
     * @param viewName
     * @return colTypes
     */
    public JSONObject getColTypes(String viewName) {

        JSONObject ret = new JSONObject();
        String selectSql = String
                .format("select distinct * from %s limit 5", viewName);
        JSONArray queryResult = gpDataProvider.executeQuery(selectSql);
        JSONArray headers = gpDataProvider.queryTableMeta(selectSql);

        JSONObject colTypes = new JSONObject();
        JSONObject filterCols = new JSONObject();
        for (int i = 0; i < headers.size(); i++) {
            String col = headers.getJSONObject(i).getString("name");
            if (col.equals("_record_id_")) {
                continue;
            }
            int type = headers.getJSONObject(i).getInteger("type");
            type = SqlHelper.mergeSqlType(type);
            switch (type) {
                case Types.VARCHAR:
                    boolean dateFlag = false;
                    for (Object obj : queryResult) {
                        JSONObject json = (JSONObject) JSONObject.toJSON(obj);
                        String data =  null == json.getString(col)?"":json.getString(col);
                        if (DatasetUtil.isTime(data)) {
                            dateFlag = true;
                            break;
                        }
                    }
                    if (dateFlag) {
                        colTypes.put(col, Types.DATE);
                        break;
                    }

                    selectSql = String
                            .format("select distinct \"%s\" from %s", col, viewName);
                    Set<String> distincts = gpDataProvider.executeQueryAsOneSet(selectSql);
                    filterCols.put(col, distincts);
                    break;
                case Types.DECIMAL:
                    colTypes.put(col, type);
                    break;
            }
        }

        ret.put("colTypes", colTypes);
        ret.put("filterCols", filterCols);
        return ret;
    }

    /**
     * 返回view表中数值类型的列的min,max,avg值
     *
     * @param viewName
     * @return GisNumericalResVO
     */
    public GisNumericalResVO statistics(String viewName, String col, String filterSql, Integer precision, boolean isDate, String dateUnit) {
        GisNumericalResVO res = new GisNumericalResVO();
//        String dataSourceKey = (String) servletContext.getAttribute(Constant.DEFAULT_DATA_SOURCE_KEY);
        String sql = "";
        if (isDate) {
            String format = "";
            float factor = 1;
            switch (dateUnit) {
                case "s":
                    format = PythonDateTypeFormatEnum.FORMAT_0.getVal();
                    break;
                case "m":
                    format = PythonDateTypeFormatEnum.FORMAT_1.getVal();
                    factor = 60;
                    break;
                case "h":
                    format = PythonDateTypeFormatEnum.FORMAT_2.getVal();
                    factor = 60 * 60;
                    break;
                case "d":
                    format = PythonDateTypeFormatEnum.FORMAT_3.getVal();
                    factor = 60 * 60 * 24;
                    break;
                case "M":
                    format = PythonDateTypeFormatEnum.FORMAT_4.getVal();
                    factor = 60 * 60 * 24 * 30;
                    break;
                case "y":
                    format = PythonDateTypeFormatEnum.FORMAT_5.getVal();
                    factor = 60 * 60 * 24 * 365;
                    break;
            }
            String standardCol = String.format("pipeline.sys_func_format_time(\"%s\"::varchar, '%s')", col, PythonDateTypeFormatEnum.FORMAT_0.getVal());
            col = String.format("pipeline.sys_func_format_time(\"%s\"::varchar, '%s')", col, format);

            sql = getDateStat(col, standardCol, filterSql, viewName, factor, precision);
//            JSONArray queryResult = gpDataService.executeQuerySQL(dataSourceKey, sql, JSONArray.class).getResult();
            JSONArray queryResult = gpDataProvider.executeQuery(sql);
            JSONObject json = (JSONObject) JSONObject.toJSON(queryResult.getJSONObject(0));
            res.setMinRes(json.getString("min"));
            res.setMaxRes(json.getString("max"));
            res.setDiffRes(json.getString("diff"));
        } else {
            sql = getNumericalStat(col, filterSql, viewName, precision);
//            JSONArray queryResult = gpDataService.executeQuerySQL(dataSourceKey, sql, JSONArray.class).getResult();
            JSONArray queryResult = gpDataProvider.executeQuery(sql);
            JSONObject json = (JSONObject) JSONObject.toJSON(queryResult.getJSONObject(0));
            res.setMinRes(json.getString("min"));
            res.setMaxRes(json.getString("max"));
            res.setDiffRes(json.getString("diff"));
            res.setAvgRes(json.getString("avg"));
            res.setDistinctCount(queryFromDB(getDistinctCount(viewName, filterSql)).get(0));
        }
        return res;
    }


    /**
     * 组合geojson+datajson
     *
     * @param type
     * @param geojson
     * @param datajson
     * @return json
     */
    public JSONObject createGeojson(String type, JSONObject geojson, JSONObject datajson) {
        JSONObject res = new JSONObject();
        res.put("type", type);
        res.put("geojson", geojson);
        res.put("datajson", datajson);
        return res;
    }

    /**
     * 生成全局分析计算后的view名
     *
     * @param viewName
     * @param projectId
     * @param tabId
     * @param layerId
     * @param checkedColName
     * @param method
     * @return view name
     */
    public String getNewView(String viewName, Long projectId, Long tabId, Long layerId, String checkedColName, String method, String unit, Integer precision) {
        List<String> viewData = calMethod(viewName, projectId, tabId, layerId, checkedColName, method, unit, precision);
        String newViewName = viewData.get(0);
        String calAreaSql = viewData.get(1);
        updateDB(calAreaSql);
        return newViewName;
    }

    /**
     * 执行数理分析方法
     *
     * @param viewName
     * @param projectId
     * @param tabId
     * @param layerId
     * @param checkedColName
     * @param method
     * @return
     */
    public List<String> calMethod(String viewName, Long projectId, Long tabId, Long layerId, String checkedColName, String method, String unit, Integer precision) {
        switch (method) {
            case "area":
                return calAreaSql(viewName, projectId, tabId, layerId, checkedColName, unit, precision);
            case "perimeter":
                return calPerimeterSql(viewName, projectId, tabId, layerId, checkedColName, unit, precision);
            case "length":
                return calLengthSql(viewName, projectId, tabId, layerId, checkedColName, unit, precision);
        }
        return null;
    }

    /**
     * queryOldGeomType
     *
     * @param tbName
     * @return type
     */
    public String queryGeomType(String tbName) {
        String getTypeSql = geomTypeSql(tbName);
        List<String> data = queryFromDB(getTypeSql);
        String type = data.get(0);
        String new_type = GIS_TYPE_DIC.get(type).toString();
        return new_type;
    }

    public String queryOriginGeomType(String tbName) {
        String getTypeSql = geomTypeSql(tbName);
        List<String> data = queryFromDB(getTypeSql);
        String originGeomType = data.get(0);
        return originGeomType;
    }

    /**
     * queryOldGeomType
     *
     * @param tbName
     * @return type
     */
    public String queryNewGeomType(String tbName) {
        String getTypeSql = newGeomTypeSql(tbName);
        List<String> data = queryFromDB(getTypeSql);
        String type = data.get(0);
        String new_type = GIS_TYPE_DIC.get(type).toString();
        return new_type;
    }

    /**
     * 获取预览几何中心的数据
     *
     * @param viewName
     * @param projectId
     * @param tabId
     * @param layerId
     * @return view名
     */
    public String previewCentoridData(String viewName, Long projectId, Long tabId, Long layerId) {
        String queryViewSql = queryViewCol(viewName);
        List<String> viewCols = queryFromDB(queryViewSql);
        if (viewCols.contains("geom") == true) {
            viewCols.remove("geom");
        }
        String cols = viewCols.stream().map(s -> "\"" + s + "\"").collect(Collectors.joining(", "));
        List<String> centroidData = calCentroidSql(viewName, projectId, tabId, layerId, cols);
        String newViewName = centroidData.get(0);
        String previewCentoridSql = centroidData.get(1);
        updateDB(previewCentoridSql);
        return newViewName;
    }

    /**
     * 获取预览缓冲区的数据
     *
     * @param viewName
     * @param projectId
     * @param tabId
     * @param layerId
     * @param radius
     * @param unit
     * @return
     */
    public String previewBufferData(String viewName, Long projectId, Long tabId, Long layerId, double radius, String unit) {
        String queryViewSql = queryViewCol(viewName);
        List<String> viewCols = queryFromDB(queryViewSql);
        if (viewCols.contains("geom") == true) {
            viewCols.remove("geom");
        }
        String cols = viewCols.stream().map(s -> "\"" + s + "\"").collect(Collectors.joining(", "));
        double newRadius = bufferUnitConverter(radius, unit);
        List<String> bufferData = calBufferSql(viewName, projectId, tabId, layerId, cols, newRadius);
        String newViewName = bufferData.get(0);
        String previewBufferSql = bufferData.get(1);
        updateDB(previewBufferSql);
        return newViewName;
    }

    /**
     * 全局累加计算
     *
     * @param newViewName
     * @param checkedColName
     * @param unit
     * @return 全局结果
     */
    public Float addAll(String newViewName, String checkedColName, String unit) {
        String result = queryFromDB(addAllSql(newViewName, checkedColName)).get(0);
        Float newResult = Float.valueOf(result);
//        Float res = unitConverter(newResult, unit);
        return newResult;
    }

    /**
     *
     * @param tbName
     * @return json
     */
    public JSONObject geojsonConverter(String tbName) {
        String geojsonSql = geomToGeojsonSql(tbName);
        List<String> data = queryFromDB(geojsonSql);
        if (ObjectUtil.isNotEmpty(data)) {
            JSONObject json = JSONObject.parseObject(data.get(0));
            //循环json处理并保存
            Iterator<Object> features = json.getJSONArray("features").iterator();
            while (features.hasNext()) {
                JSONObject next = (JSONObject) features.next();
                Object id = next.get("record_id");
                next.put("id", id);
                next.remove("record_id");
                next.getJSONObject("properties").remove("geom");
                next.getJSONObject("properties").remove("geometry");
                next.getJSONObject("properties").remove("geom_4326");
                next.getJSONObject("properties").remove("_record_id_");
                next.getJSONObject("properties").remove("record_id");

            }
            return json;
        }
        return new JSONObject();
    }

    public JSONObject geoJsonConverter(String viewName, String tbName) {
        List<SliceDTO> sliceDTOS = getSliceDTO(tbName, null, null);
        JSONObject totalResult = null;
        AtomicInteger rateOfProcess = new AtomicInteger();
        for(SliceDTO sliceDTO : sliceDTOS) {
            JSONObject elementResult = geoJsonConverterSlice(viewName, sliceDTO);
            totalResult = mergeGeoJson(totalResult, elementResult);
            logger.info(String.format("query gis geom json  %s/%s", rateOfProcess.incrementAndGet(), sliceDTOS.size()));
        }
        return totalResult;
    }

    public void preloadSliceGeoJsonSliceData(String viewTable, String sourceTable, SliceDTO requestSliceDTO,
            Long sliceBytes, Long maxSliceNumbers) {
        List<SliceDTO> sliceDTOS = getSliceDTO(sourceTable, sliceBytes, maxSliceNumbers);
        int partitionSize = Math.max(sliceDTOS.size() / PRE_LOAD_PARTITION_NUMBERS, 1);
        int belongPartition = requestSliceDTO.getIndex() / partitionSize;

        List<CompletableFuture<Void>> completableFutureList = sliceDTOS.stream()
                .filter(sliceDTO -> {
                    if (sliceDTO.getIndex() < belongPartition * partitionSize
                            || sliceDTO.getIndex() >= (belongPartition + 1) * partitionSize) {
                        return false;
                    }
                    if (redisUtil.hasKey(constructGeoJsonCacheKey(viewTable, sliceDTO))) {
                        return false;
                    }
                    return true;
                })
                .map(sliceDTO -> CompletableFuture.runAsync(
                        () -> geoJsonConverterSlice(viewTable, sliceDTO), basePool.getExecutor())
                )
                .collect(Collectors.toList());
        try {
            CompletableFuture.allOf(completableFutureList.toArray(new CompletableFuture[completableFutureList.size()]))
                    .get();
        } catch (ExecutionException | InterruptedException e) {
            logger.info("preload slice geo json failed", e);
        }
    }

    public JSONObject geoJsonConverterSlice(String viewName, SliceDTO sliceDTO) {
        String cacheKey = constructGeoJsonCacheKey(viewName, sliceDTO);
        Object cacheResult = redisUtil.get(cacheKey);
        //缓存命中
        if(Objects.nonNull(cacheResult)) {
            return (JSONObject) cacheResult;
        }
        List<String> columnNames = getColumnNames(viewName);
//        String dataSourceKey = (String) servletContext.getAttribute(Constant.DEFAULT_DATA_SOURCE_KEY);
//        DataPattern dataPattern = new Table(dataSourceKey, viewName.split("\\.")[0], viewName.split("\\.")[1]);
        String geoJsonSql = geomToGeoJsonSql(viewName, columnNames, sliceDTO.getOffset(), sliceDTO.getPageSize());
//        JSONArray result = gpDataService.executeQuerySQL(dataPattern, geoJsonSql, JSONArray.class).getResult();
        JSONArray result = gpDataProvider.executeQuery(geoJsonSql);
        JSONObject geoJsonResult = constructGeoJson(result);
        //分片数据添加进缓存，过期时间设置为1hour
        redisUtil.set(cacheKey, geoJsonResult, SLICE_GEOJSON_EXPIRE_TIME);
        return geoJsonResult;
    }

    /**
     * 将数据库中查出来的数据转化成GeoJson
     * GeoJson格式可参考: <a href="https://zhuanlan.zhihu.com/p/510882183">GeoJson格式详解</a>
     * @param jsonArray jsonArray from database
     * @return GeoJson JSONObject
     */
    private JSONObject constructGeoJson(JSONArray jsonArray) {
        if(Objects.isNull(jsonArray)) {
            return null;
        }
        JSONObject featureCollection = new JSONObject();
        featureCollection.put("type", "FeatureCollection");

        JSONArray features = new JSONArray();
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject element = jsonArray.getJSONObject(i);
            JSONObject feature = new JSONObject();
            feature.put("type", "Feature");
            feature.put("geometry", element.getJSONObject("geo_json"));
            feature.put("id", element.getLong("_record_id_"));
            element.remove("_record_id_");
            element.remove("geo_json");
            feature.put("properties", element);
            features.add(feature);
        }

        featureCollection.put("features", features);

        return featureCollection;
    }

    /**
     * 把geoJsonTwo merge进genJsonOne
     * @param geoJsonOne
     * @param geoJsonTwo
     */
    private JSONObject mergeGeoJson(JSONObject geoJsonOne, JSONObject geoJsonTwo) {
        if(Objects.isNull(geoJsonOne)) {
            return geoJsonTwo;
        }
        if(Objects.isNull(geoJsonTwo)) {
            return geoJsonOne;
        }
        geoJsonOne.getJSONArray("features").addAll(geoJsonTwo.getJSONArray("features"));
        return geoJsonOne;
    }

    public List<String> getColumnNames(String tbName) {
        String countSql = getTbColumnName(tbName);
        List<String> columnNames = queryFromDB(countSql);
        return columnNames.stream().filter(columnName -> !GEO_JSON_UNUSED_COLUMNS.contains(columnName))
                .collect(Collectors.toList());
    }

    public List<SliceDTO> getSliceDTO(String tableName, Long sliceBytes, Long maxSliceNumbers) {
        if(Objects.isNull(sliceBytes) || sliceBytes <= 0) {
            sliceBytes = DEFAULT_QUERY_SLICE_BYTES;
        }
        if(Objects.isNull(maxSliceNumbers) || maxSliceNumbers <= 0) {
            maxSliceNumbers = DEFAULT_QUERY_MAX_SLICE_NUMBERS;
        }

        String cacheKey = constructGisSliceCacheKey(tableName, sliceBytes, maxSliceNumbers);
        Object cacheResult = redisUtil.get(cacheKey);
        if(Objects.nonNull(cacheResult)) {
            return JSON.parseArray((String)cacheResult, SliceDTO.class);
        }

        List<SliceDTO> sliceDTOS = Lists.newArrayList();
        List<Long> sizeAndTotalRow = datasetService.getSizeAndTotalRow(tableName);
        long size = sizeAndTotalRow.get(0);
        long totalRow = sizeAndTotalRow.get(1);
        //数据量小于等于切片大小时，不进行切片
        if (size <= sliceBytes) {
            sliceDTOS.add(new SliceDTO(0, 0, totalRow));
        } else {
            //gis表中一行数据可能大于切片大小
            long sliceNumber = Math.min(Math.min(size / sliceBytes, maxSliceNumbers), totalRow);
            long pageSize = totalRow / sliceNumber;
            int sliceIndex = 0;
            long offset = 0;
            do {
                long currentPageSize = Math.min(pageSize, totalRow - offset);
                sliceDTOS.add(new SliceDTO(sliceIndex++, offset, currentPageSize));
                offset += currentPageSize;
            } while (offset < totalRow);
        }
        redisUtil.set(cacheKey, JSON.toJSONString(sliceDTOS), SLICE_META_DATA_EXPIRE_TIME);
        return sliceDTOS;
    }


    /**
     * tbTogeoJson
     *
     * @param tbName
     * @return json
     */
    public JSONObject geojsonConverterKG(String tbName, Long projectId, String type, Long layerId) {
        String paletteKey = tbName + "_" + layerId;
        //todo color options
        List<String> colors = (List<String>) getColors().get(0);
        List<String> colors_polygon = (List<String>) getColors().get(1);
        String geojsonSql = geomToGeojsonSql(tbName);
        List<String> data = queryFromDB(geojsonSql);

        DashboardDTO dashboard = new DashboardDTO();
        dashboard.setProjectId(projectId);
        dashboard.setType("kg");
        DashboardDTO curKG = dashboardMapper.queryKGDashboard(dashboard);
        JSONArray vids = new JSONArray();
        JSONArray eids = new JSONArray();
        try {
            JSONObject kgFiltered = JSONObject.parseObject(curKG.getLayout()).getJSONObject("knowledge_graph");

            vids = kgFiltered.getJSONArray("vids");
            eids = kgFiltered.getJSONArray("eids");
            if (vids == null) {
                vids = kgFiltered.getJSONArray("vertices");
            }
            if (eids == null) {
                eids = kgFiltered.getJSONArray("edges");
            }
        } catch (Exception e) {
            logger.error(e.toString());
        }
        if (ObjectUtil.isNotEmpty(data)) {
            JSONObject json = JSONObject.parseObject(data.get(0));
            JSONObject resJson = new JSONObject();
            JSONArray resFeature = new JSONArray();
            //循环json处理并保存
            Iterator<Object> features = json.getJSONArray("features").iterator();
            //HashMap<String, String> map = new HashMap<>();
            JSONObject layerPalette = new JSONObject();
            JSONObject layout = JSONObject.parseObject(curKG.getLayout());
            if (layout.containsKey("palette") && layout.getJSONObject("palette").containsKey(paletteKey)) {
                // map = (HashMap<String, String>) layout.getJSONObject("palette").get(tbName);
                layerPalette = layout.getJSONObject("palette").getJSONObject(paletteKey);
            }
            Integer colorIter = 0;
            while (features.hasNext()) {
                JSONObject next = (JSONObject) features.next();
                //Object id = next.get("record_id");
                JSONObject props = next.getJSONObject("properties");
                Object id = new Object();
                try {
                    id = props.getInteger("id");
                } catch (Exception e) {
                    id = props.getString("id");
                    logger.error("get ID error whenn plotting gis layer");
                }
                next.put("id", id);
                if (props.containsKey("class")) {
                    String cls = props.getString("class");
                    if (!layerPalette.containsKey(cls)) {
                        if (type.equals("circle")) {
                            layerPalette.put(cls, colors.get(colorIter));
                        }
                        if (type.equals("fill")) {
                            layerPalette.put(cls, colors_polygon.get(colorIter));
                        }

                        colorIter += 1;
                    }
                    next.getJSONObject("properties").put("color", layerPalette.get(cls));
                }
                Boolean noRelFilter = false;
                if (type.equals("line")) {
                    JSONArray layerData = layout.getJSONArray("layerData");
                    for (int indexld = 0; indexld < layerData.size(); indexld++) {
                        JSONObject singleLayerData = layerData.getJSONObject(indexld);
                        if (singleLayerData.getLong("id").equals(layerId) && singleLayerData.containsKey("color")) {
                            next.getJSONObject("properties").put("color", singleLayerData.getString("color"));
                        }
                        if (singleLayerData.getLong("id").equals(layerId) && singleLayerData.getString("source").equals("entity")) {
                            noRelFilter = true;
                        }
                    }
                }
                next.remove("record_id");
                next.getJSONObject("properties").remove("geom");
                next.getJSONObject("properties").remove("geometry");
                next.getJSONObject("properties").remove("geom_4326");
                next.getJSONObject("properties").remove("_record_id_");
                next.getJSONObject("properties").remove("geom_4326");
                next.getJSONObject("properties").remove("record_id");
                if (type.equals("circle") && vids.contains(id)) {
                    resFeature.add(next);
                }
                if ((type.equals("line") && eids.contains(id)) || noRelFilter) {
                    resFeature.add(next);
                }
                //todo comment back
                if (type.equals("fill") && props.containsKey("id") && vids.contains(props.get("id"))) {
                    resFeature.add(next);
                }
//                if (type.equals("fill")) {
//                    resFeature.add(next);
//                }
            }
            resJson.put("features", resFeature);
            resJson.put("type", json.getString("type"));
            JSONObject palette = new JSONObject();
            if (layout.containsKey("palette")) {
                palette = layout.getJSONObject("palette");
            }
            if (palette.containsKey(paletteKey)) {
                JSONObject curTBPalette = palette.getJSONObject(paletteKey);
                layerPalette.putAll(curTBPalette);
            }
            palette.put(paletteKey, layerPalette);
            layout.put("palette", palette);
            curKG.setLayout(layout.toJSONString());
            dashboardMapper.update(curKG);
            return resJson;
        }
        return new JSONObject();
    }

    public JSONArray getColors() {
        JSONArray colorJsonObject = new JSONArray();
        List<String> colors = Lists.newArrayList("violet", "brown", "monte", "yellow", "magenta", "lynch", "sienna", "slateBlue", "magenta", "silver", "red", "meadow");
        List<String> colors_polygon = Lists.newArrayList("slateBlue", "silver", "red", "cornflower", "meadow", "lynch", "yellow", "monte", "magenta", "sienna", "violet", "brown");
        colorJsonObject.add(colors);
        colorJsonObject.add(colors_polygon);
        return colorJsonObject;
    }

    /**
     * tbTogeoJson
     *
     * @param tbName
     * @return json
     */
    public JSONObject geojsonConverterKG_Publish(String tbName, Long projectId, String type, JSONObject dynamicFilters, Long layerId) {
        String paletteKey = tbName + "_" + layerId;
        //todo color options
        List<String> colors = (List<String>) getColors().get(0);
        List<String> colors_polygon = (List<String>) getColors().get(1);

        String geojsonSql = geomToGeojsonSql(tbName);
        List<String> data = queryFromDB(geojsonSql);

        DashboardDTO dashboard = new DashboardDTO();
        dashboard.setProjectId(projectId);
        dashboard.setType("kg");
        DashboardDTO curKG = dashboardMapper.queryKGDashboard(dashboard);
        JSONArray vids = new JSONArray();
        JSONArray eids = new JSONArray();
        boolean hasFilters = true;
        JSONObject kgFiltered;
        try {
            if (dynamicFilters == null) {
                kgFiltered = JSONObject.parseObject(curKG.getPublishLayout()).getJSONObject("knowledge_graph");
            } else {
                kgFiltered = dynamicFilters;
            }


            if (null == kgFiltered) {
                hasFilters = false;
            }//todo
            vids = kgFiltered.getJSONArray("vids");
            eids = kgFiltered.getJSONArray("eids");
            if (vids == null) {
                vids = kgFiltered.getJSONArray("vertices");
            }
            if (eids == null) {
                eids = kgFiltered.getJSONArray("edges");
            }
        } catch (Exception e) {
            logger.error(e.toString());
        }
        if (ObjectUtil.isNotEmpty(data)) {
            JSONObject json = JSONObject.parseObject(data.get(0));
            JSONObject resJson = new JSONObject();
            JSONArray resFeature = new JSONArray();
            //循环json处理并保存
            Iterator<Object> features = json.getJSONArray("features").iterator();
            JSONObject layerPalette = new JSONObject();
            //palette
            JSONObject layout_publish = JSONObject.parseObject(curKG.getPublishLayout());
            if (layout_publish.containsKey("palette") && layout_publish.getJSONObject("palette").containsKey(paletteKey)) {
                layerPalette = layout_publish.getJSONObject("palette").getJSONObject(paletteKey);
            }

            while (features.hasNext()) {
                JSONObject next = (JSONObject) features.next();
                JSONObject props = next.getJSONObject("properties");
                Object id = new Object();
                try {
                    id = props.getInteger("id");
                } catch (Exception e) {
                    id = props.getString("id");
                    logger.error("get ID error whenn plotting gis layer");
                }
                next.put("id", id);
                if (props.containsKey("class")) {
                    String cls = props.getString("class");
                    if (!layerPalette.containsKey(cls)) {
                        //todo error
                    }
                    next.getJSONObject("properties").put("color", layerPalette.get(cls));
                }
                Boolean noRelFilter = false;
                if (type.equals("line")) {
                    JSONArray layerData = layout_publish.getJSONArray("layerData");
                    for (int indexld = 0; indexld < layerData.size(); indexld++) {
                        JSONObject singleLayerData = layerData.getJSONObject(indexld);
                        if (singleLayerData.getLong("id").equals(layerId) && singleLayerData.containsKey("color")) {
                            next.getJSONObject("properties").put("color", singleLayerData.getString("color"));
                        }
                        if (singleLayerData.getLong("id").equals(layerId) && singleLayerData.getString("source").equals("entity")) {
                            noRelFilter = true;
                        }
                    }
                }
                next.remove("record_id");
                next.getJSONObject("properties").remove("geom");
                next.getJSONObject("properties").remove("geometry");
                next.getJSONObject("properties").remove("geom_4326");
                next.getJSONObject("properties").remove("_record_id_");
                next.getJSONObject("properties").remove("geom_4326");
                next.getJSONObject("properties").remove("record_id");
                if (type.equals("circle") && (vids.contains(id) || hasFilters == false)) {
                    resFeature.add(next);
                }
                if ((type.equals("line") && (eids.contains(id) || hasFilters == false)) || noRelFilter) {
                    resFeature.add(next);
                }
                //todo may temporarily fix the bug
                if (type.equals("fill") && props.containsKey("id") && vids.contains(props.get("id"))) {
                    resFeature.add(next);
                }
//                if (type.equals("fill")) {
//                    resFeature.add(next);
//                }
            }
            resJson.put("features", resFeature);
            resJson.put("type", json.getString("type"));
            return resJson;
        }
        return new JSONObject();
    }


    /**
     * 根据当前图层寻找目标图层中最近的N个点并创建新图层
     *
     * @param currentViewName
     * @param targetViewName
     * @param elementId
     * @param neighbors
     * @return nearLayerViewName
     */
    public String getNearElementsLayer(Long projectId, Long tabId, Long layerId, String currentViewName, String targetViewName, Long elementId, Integer neighbors) {
        String getNearElementsIdSql = getNearElementsId(currentViewName, targetViewName, elementId, neighbors);
        List<String> Ids = queryFromDB(getNearElementsIdSql);
        String newIds = String.join(",", Ids);
        List<String> viewData = createLayerByNearIds(projectId, tabId, layerId, targetViewName, newIds);
        String sql = viewData.get(1);
        updateDB(sql);
        return viewData.get(0);
    }

    /**
     * 返回图层元素个数
     *
     * @param targetViewName
     * @return
     */
    public Integer getNumberOfElements(String targetViewName) {
        String getTotalElementsSql = countElements(targetViewName);
        String numberOfElements = queryFromDB(getTotalElementsSql).get(0);
        Integer newResult = Integer.valueOf(numberOfElements);
        return newResult;
    }


    /**
     * 连接gp数据库并queryData
     *
     * @param sql
     * @return queryDbData
     */
    @SuppressWarnings({"all"})
//    public List<String> queryFromDB(String sql) {
//        logger.info("going to execute sql -> " + sql);
//        String dataSourceKey = (String) servletContext.getAttribute(Constant.DEFAULT_DATA_SOURCE_KEY);
//        try {
//            RpcResult<List> listRpcResult = gpDataService.executeSlowSQL(dataSourceKey, sql, List.class);
//            if (listRpcResult.isRelocated()) {
//                Thread.sleep(2000);
//                JSONArray jsonArray = (JSONArray) redisUtil.get(listRpcResult.getMessage());
//                return jsonArray.toJavaList(String.class);
//            } else {
//                List result = listRpcResult.getResult();
//                return (List<String>) result.stream().flatMap(Object::toString).collect(Collectors.toList());
//            }
//        } catch (Exception e2) {
//            logger.error("Gis Executing QuerySQL Error", e2);
//        }
//        return Lists.newArrayList();
//    }

    public List<String> queryFromDB(String sql) {
        Connection conn = null;
        List<String> data = new ArrayList<>();
        try {
            conn = gpDataProvider.getConn(DEFAULT_DATASET_ID);
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()) {
                String val = rs.getString(1);
                data.add(val);
            }
            st.close();
        } catch (SQLException e1) {
            logger.error("Syntax Error While Executing " + sql + " STATEMENT", e1);
        } catch (Exception e2) {
            logger.error("Gis Executing QuerySQL Error", e2);
        } finally {
            JDBCUtil.close(conn, null, null);
        }
        return data;
    }


    /**
     * @param sql
     */
    public void updateDB(String sql) {
        Connection conn = null;
        try {
//            String dataSourceKey = (String) servletContext.getAttribute(Constant.DEFAULT_DATA_SOURCE_KEY);
//            gpDataService.executeUpdateSQL(dataSourceKey, sql);
            conn = gpDataProvider.getConn(DEFAULT_DATASET_ID);
            Statement st = conn.createStatement();
            st.executeUpdate(sql);
            st.close();
        } catch (SQLException e1) {
            logger.error("Syntax Error While Executing " + sql + " STATEMENT", e1);
        } catch (Exception e2) {
            logger.error("Gis Executing UpdateSQL Error", e2);
        }
        finally {
            JDBCUtil.close(conn, null, null);
        }

    }

    public List<GisLayersVO> pointToLine(String col, String orderCol, String source, String layerName, Long projectId, Long layerId, Long tabId, boolean hasArrow) {
        long userId = JwtUtil.getCurrentUserId();
        Long timeStamp = System.currentTimeMillis();
        String[] parts = source.split("_");
        String lastTimeStamp = parts[parts.length - 1];
        String target = source.replace(lastTimeStamp, String.valueOf(timeStamp)) + "_line";
        String sql = "";
        if (col.isEmpty()){
            sql = GisUtil.pointToLineSql(SqlUtil.formatPGSqlColName(orderCol), source, target);
        }else {
            sql = GisUtil.pointToLineSql(SqlUtil.formatPGSqlColName(col), SqlUtil.formatPGSqlColName(orderCol), source, target);
        }
        updateDB(sql);

        List<Long> Ids = gisLayersService.queryLayerIds(userId, tabId);
        gisLayersService.updateAnalysisOrder(Ids, userId, tabId);
        String sourceTbName = gisLayersService.queryTbNameByLayerId(layerId);
        String type = queryNewGeomType(target);
        GisLayersDTO gisLayer = gisLayersService.addAnalysisLayer(projectId, userId, tabId, sourceTbName, layerName, type);
        gisLayer.setViewTable(target);
        JSONObject data = new JSONObject();
        data.put("hasArrow", hasArrow);
        gisLayer.setDataJson(data.toJSONString());
        gisLayersService.update(gisLayer);
        List<GisLayersVO> layers = new ArrayList<>();
        gisLayersService.queryLayersByTabId(tabId).forEach(e -> {
            layers.add(e.layer());
        });

        return layers;
    }

}
